classdef Insert_dev < SQLstatement_dev
  % Class for simple (unconditioned) insert statements
  % Derived from SQLstatement_dev
  % C-tor sig: iObject = Insert_dev(con, parameters); 
  %     for details, type help('Insert_dev.Insert_dev')
  %
  % Author: David Goldsmith, Wash. State Dept. of Ecology, dgol461@ecy.wa.gov
  % Release date: 
  
  % TODO: Deal w/ invalid data if cols empty when tbls changes;
  %       and when cols changes to non-empty after a tbls change, but data is invalid 
  %       'cause it was set when cols was empty for a previous table.
  %       Question: are both of the above problems precluded if I simply enforce 
  %       cols empty => data empty?

    properties
      % Inherited from SQLstatement_dev
%         wrappedDBO;
%         con; cat; sch; meta;
%         tbls = {};
%         aCc = {}; % cell array of available columns, w/out info as to their source table(s)
%         pkey;
%         result = {};
%         issuedStatementError = false;
%         issuedAvailableError = false;
%         errCode = {};
%         duration;
%     (Dependent = true)
%         DBname;
%         statement;
%         availableTables;
%         availableColumns;
        cols = {};
        data;
    end
    
    properties (SetAccess = protected)
        CSD = struct(...% CSD = Common Select "Decorators"
              'colEquivalents', {{'*', 'GETDATE()', 'GETUTCDATE()'}},...
              'globalModifiers', {{'ALL', 'DISTINCT', 'TOP \d+'}},...
              'singleColFuncs', {{'AVG', 'COUNT', 'MAX', 'MEDIAN', 'MIN', 'STDDEV', 'SUM', 'VARIANCE'}},...
              'dateFuncs', {{'DATEADD', 'DATEDIFF'}},...
              'dateParts', {{'year', 'yy', 'yyyy', 'quarter', 'qq', 'q', 'month', 'mm', 'm',...
                             'dayofyear', 'dy', 'y', 'day', 'dd', 'd', 'week', 'wk', 'ww', 'hour',...
                             'hh', 'minute', 'mi', 'n', 'second', 'ss', 's', 'millisecond', 'ms'}},...
              'as', {{'AS'}});
        allCSD = {};
    end
        
    methods % Accessible, i.e., non-private
      % Inherited from SQLstatement_dev
%         SQLstatement_dev(DBname, un, pwd, devVerOverride)   
%         assembleStatement(obj) (Abstract)
%         delete(obj)             
%         procErr(obj)
%         setPropVals(obj, prop, elements, extend) (Abstract) 
%         submitStatement(obj) (Abstract) 

        function iObject = Insert_dev(con, parameters)
          % The insert object's constructor
          %
          % Arguments: (req = required, opt = optional, ca = cell array)
          %
          %         con - DBwrapper_dev-wrapped Matlab database object, opt,
          %               if included, may be empty
          %
          %   parameters - struct, opt; the insertion parameters, in the following fields:
          %
          %      + cols    - ca of ca* of string(s), opt; 
          %                  the column(s) into which one wants to place the data
          %
          %      + tbls    - ca of ca containing a single string, opt; 
          %                  the (single) table where those columns are to be found
          %
          %      + data    - a struct, opt; the data to be inserted; 
          %                  the struct fieldnames must exactly correspond to the elements of cols
          %                  the field values must be "vectors," all of the same length
          %                  Note: other than to confirm that data is a struct w/ fieldnames 
          %                  matching requested columns, no data format QAQC is done here; 
          %                  it is assumed that any such deemed necessary is done by whatever 
          %                  creates the input data struct--if there is a problem w/ the data, 
          %                  a database insertion exception will be caught and reported
          %
          % *Note: the "ca of ca" syntax is required as a consequence of the way Matlab constructs 
          %        and interprets ca-valued fields in structures; an illustrative example will
          %        hopefully suffice to explain this.  For an example, see query.m's documentation.
          %

            if nargin < 1, con = ''; end
            if nargin < 2, parameters = struct.empty; end
            
            iObject = iObject@SQLstatement_dev(con);
            for f = fields(iObject.CSD)'
                iObject.allCSD = union(iObject.allCSD, iObject.CSD.(f{:}));
            end

            paraFields = fieldnames(parameters);
            for prop={'tbls', 'cols', 'data'}
                prop = prop{:};
                if ismember(prop, paraFields)
                    iObject.setPropVals(prop, parameters.(prop));
                end
            end
            
        end
        
        function elements = validateInput(obj, prop, elements, extend)
            
            global lfcr
            
            switch prop
                
                case 'tbls'
                    if ~isempty(obj.tbls)
                        obj.errCode{end+1} = ['insert.setPropVals error: insert object re-use for table change '...
                                              'is broken; please use a new insert object if changing tables'];
                    elseif ~iscellstr(elements)
                        obj.errCode{end+1} = 'insert.setPropVals error: wrong type for tbls';
                    elseif numel(elements)~=1
                        obj.errCode{end+1} = 'insert.setPropVals error: received more than one value for tbls';
                    elseif extend && numel(obj.tbls)
                        obj.errCode{end+1} = ['insert.setPropVals error: asked to make tbls contain more than '...
                                              'one element; this is not allowed in a simple insertion, please '...
                                              'use the comIns class'];
                    elseif ~any(ismember(elements{1}, obj.availableTables))
                        obj.errCode{end+1} = ['insert.setPropVals error: invalid table ' elements{1}];
                    end
                    
                case 'cols'
                    if ~(isempty(elements) || iscellstr(elements))
                        obj.errCode{end+1} = 'insert.setPropVals error: wrong type input for cols';
                    else
                        obj.confirmColAvail(elements);
                    end
                    
                case 'data'
                    if isempty(obj.cols)
                        obj.errCode{end+1} = ['insert.validateInput(data) error: cols property empty; '...
                                              'can''t confirm consistency' lfcr 'w/ data struct '...
                                              'fieldnames, so leaving data property unset'];
                    elseif ~(isstruct(elements) && setequal(fieldnames(elements), obj.cols))
                        obj.errCode{end+1} = ['insert.validateInput(data) error: data must be a '...
                                             'struct whose set of fieldnames exactly' lfcr ...
                                             'matches the requested columns, (i.e., this insert '...
                                             'object''s cols property).'];
                    end
                    
                otherwise
                    obj.errCode{end+1} = ['insert.setPropVals error: invalid prop ' prop];
                    
            end    
        end

        function confirmColAvail(obj, cols)
            if isempty(obj.tbls)
                obj.errCode{end+1} = ['insert.setPropVals(cols) error: tbls property unset, can''t '...
                                      'assure availability of any columns, so not setting cols property'];
            elseif ~isempty(obj.pkey) && ismember(obj.pkey, cols)
                obj.errCode{end+1} = ['insert.setPropVals(cols) error: insert object cols property '...
                                      'can''t contain column ' obj.pkey ' because it is table '...
                                      obj.tbls{:} '''s primary key'];
            else
                obj.availableColumns; % Make sure obj.aCc is up-to-date
                unavailCols = cols(~cellstrIsMember(cols, obj.aCc));
                if ~isempty(unavailCols)
                    obj.errCode{end+1} = sprintf('insert.setPropVals(cols) error: columns %s, ',...
                                                 unavailCols{1:end-1});
                    obj.errCode{end} = [obj.errCode{end} sprintf('%s aren''t available in ',...
                                                 unavailCols{end}) ' table ' obj.tbls{:}];
                end
            end
        end

        function setPropVals(obj, prop, elements, extend)
          % Sets property values, enforcing content restrictions => use instead of direct assignment
          % extend is an optional logical indicating whether to replace (false, the default) or 
          % supplement (true) obj.(prop) with elements
            if nargin < 4
                extend = false;
            end
            
          % Refresh error code container
            obj.errCode = {};
            
          % Validate inputs
            elements = obj.validateInput(prop, elements, extend);
            
            if isempty(obj.errCode) || all(~cellfun(@isempty, regexp(obj.errCode, 'ALERT')))
              % If changing the table, save the current list of valid columns
                if strcmp(prop, 'tbls') && ~isempty(obj.tbls)
                    obj.availableColumns; % update obj.aCc
                    oldValidCols = obj.aCc;
                end
              
              % The "Action"
                if extend && ~strcmp(prop, 'data') % For now, extension of data struct not supported
                    newElements = {};
                    for e = elements
                        e = e{:};
                        addTo = ~ismember(e, obj.(prop));
                        if addTo
                            newElements{end+1} = e;
                        end
                    end
                    elements = newElements;
                    obj.(prop) = extendArray(obj.(prop), elements);
                else
                    obj.(prop) = elements;
                end
                
                if exist('oldValidCols', 'var')
                    obj.processTblsChange(oldValidCols);
                end
                
                if strcmp(prop, 'tbls')
                    obj.getTablesPKey;
                end
                
            end

        end
        
        function processTblsChange(obj, oldValidCols)
      % TODO: there's a bug TBD in my business logic somewhere
      % Workaround: DON'T RE-USE THESE WHEN CHANGING TABLES!!!
          % If changing the table, cols will almost certainly need modification, 
          % and data corresponding to removed columns will no longer be valid 
            obj.availableColumns; % make sure obj.aCc is up-to-date
          % Keep those cols which are still available
            tmpc = {};
            for i=1:numel(obj.cols)
                if ismember(obj.cols{i}, obj.aCc)
                    tmpc{end+1} = obj.cols{i};
                  % TODO: implement data retention for retained columns
                end
            end
            if ~isequal(obj.cols, tmpc)
                obj.cols = tmpc;
                obj.data = obj.data.empty;
                obj.errCode{end+1} = ['insert.setPropVals ALERT: table change required change in '...
                                      'cols property and emptying of all data!'];
            end
        end
        
        function S = assembleStatement(obj)
            S = 'Unnecessary';
        end
        
        function submitStatement(obj)
          % First, make sure auto-commit is off and "assume the worst"
            set(obj.con, 'AutoCommit', 'off')
            success = -1; % Corresponds to all tryInsert attempts throwing an exception
            %  "    = 1 => tryInsert didn't throw an exception and its result passed the QA check
            %  "    = 0 =>     "       "      "          "     but its result failed the QA check
            % All these outcomes are handled programmatically, so the catches below don't need to
            % do anything, they're just there so that this function _can_ handle exceptions

          % Now
            try % MATLAB fastinsert--fastest (w/ documented rollback support)
                success = obj.tryInsert(@fastinsert);
            catch ME 
            end
            
            if success ~= 1
                try % MATLAB insert--not as fast, but more robust w.r.t. datatype of inputs
                    success = obj.tryInsert(@insert);
                catch ME 
                end
            end
            
          % Finally, deal w/ the outcome of our attempts
            switch success
                case 1
                    commit(obj.con)
%                    rollback(obj.con) %     Remove this line  "  "     "
                case 0
                    msg = ['Insert succeeded, but produced an incorrect result, '...
                           'which was thus rolled back.  Please investigate and/or '...
                           'notify dgol461@ecy.wa.gov'];
                    
                case -1
                    msg = getReport(ME);
            end
            
            if exist('msg', 'var')
                rollback(obj.con)
                obj.errCode{end+1} = ['insert.submitStatement failure: here''s the '...
                                      'returned error message ' msg];
            end
        
        end
        
    end
    
    methods (Access = protected)
        
        function la = isCSD(obj, arg) % returns 1 where arg is a Common Select Decorator, 
                                      % 0 where it isn't
            la = ismember(arg, obj.CSD);
        end
        
        function success = tryInsert(obj, hFunc)
          % First, build the cell array to push
            ncols = numel(obj.cols);
            nrows = size(obj.data.(obj.cols{1}), 1);
            data = cell(nrows, ncols);
            for i=1:ncols
                tmp = obj.data.(obj.cols{i});
                if iscell(tmp)
                    data(1:nrows,i) = tmp;
                elseif ischar(tmp)
                    data(1:nrows,i) = cellstr(tmp);
                else
                    data(1:nrows,i) = num2cell(tmp);
                end
            end
            
            success = -1; % make sure that success reflects the Exception-al case
                          % unless an unException-al insertion occurs
            try
                tic;
                    hFunc(obj.con, obj.tbls{:}, obj.cols, data)
                obj.duration = toc;
            catch ME
                rethrow(ME)
            end

          % If we get here, insertion didn't throw an Exception, 
          % but we still need to check the result
            success = obj.checkResult;
            if ~success
                rollback(obj.con)
            end
        end
        
        function check = checkResult(obj)
          % Assume the best
            check = true;
%             q = query_dev(obj.con, struct('tbls', {obj.tbls(:)},...
%                                           'cols', {obj.cols},...
%                                           'where', {{[obj.pkey.key '>' ...
%                                                       num2str(obj.pkey.val)]...
%                                                    }}));
%             q.submitStatement;
%             res = q.structFromResult;
%             fns = fieldnames(res);
%             for i=1:numel(fns)
%                 check = ismember(fns{i}, obj.cols);
%                 if check
%                     data = obj.data.(fns{i});
%                     if iscell(data)
%                         check = setequal(res.(fns{i}), data);
%                     else
%                         check = all([res.(fns{i}){:}]' == data);%setequal(res.(fns{i}), num2cell(data));
%                     end
%                 end
%                 if ~check
%                     break
%                 end
%             end
        end
        
    end
    
end